package sys.news.dao.impl;


import sys.news.dao.NewsDao;
import sys.news.pojo.News;
import sys.news.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements NewsDao {
    /**
     * 添加新闻内容
     *
     * @param news
     * @return
     * @throws RuntimeException
     */
    @Override
    public int addNews(News news) throws RuntimeException {
        int result = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            //1.获取连接
            conn = DBUtil.getConnection();
            //2.执行sql
            String sql = "INSERT INTO news(ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary)" +
                    " VALUE(?,?,?,?,?,?,?,?); ";
            pstmt = conn.prepareStatement(sql);
            //传递参数
            pstmt.setLong(1,news.getNtid());
            pstmt.setString(2,news.getNtitle());
            pstmt.setString(3,news.getNauthor());
            pstmt.setString(4,news.getNmodifyDate());
            pstmt.setString(5,news.getNpicPath());
            pstmt.setString(6,news.getNcontent());
            pstmt.setString(7,news.getNmodifyDate());
            pstmt.setString(8,news.getNsummary());
            //处理结果
            result = pstmt.executeUpdate();
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            DBUtil.closeAll(conn, pstmt, null);
            return result;
        }
//        String sql = "INSERT INTO news(ntid,ntitle,nauthor,ncreateDate,npicPath,ncontent,nmodifyDate,nsummary)" +
//                " VALUE(?,?,?,?,?,?,?,?);";
//        Object[] params = {news.getNtid(),news.getNtitle(),news.getNauthor(),
//                news.getNcreateDate(),news.getNpicPath(),news.getNcontent(),
//                news.getNmodifyDate(),news.getNsummary()
//        };
//        return DBUtil.update(sql,params);
    }

    /**
     * 删除新闻内容
     *
     * @param nid
     * @return
     * @throws RuntimeException
     */
    @Override
    public int delNews(int nid) throws RuntimeException {
        String sql = "DELETE FROM news WHERE nid = ?";
        Object[] params = new Object[] {nid};
        return DBUtil.update(sql,params);
    }

    /**
     * 修改新闻
     *
     * @param news
     * @return
     * @throws RuntimeException
     */
    @Override
    public int updateNews(News news) throws RuntimeException {
        int result = 0;
        Connection  conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE news SET nauthor = ? WHERE nid = ?; ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,news.getNauthor());
            pstmt.setInt(2,news.getNid());
            result = pstmt.executeUpdate();
        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn, pstmt, null);
            return result;
        }
    }

    /**
     * 查询所有新闻
     *
     * @param
     * @return
     * @throws RuntimeException
     */
    @Override
    public List<News> getAllNews() throws RuntimeException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<News> list = new ArrayList<News>();
        try{
            //1.获取连接对象
            conn = DBUtil.getConnection();
            //2.执行sql
            String sql = "SELECT nid,nauthor,ncreateDate,ntitle,npicPath FROM news;";
            //3.获取预处理对象
            pstmt = conn.prepareStatement(sql);
            //5.处理结果集
            rs = pstmt.executeQuery();
            while(rs.next()){
                News news = new News();
                news.setNid(rs.getInt("nid"));
                news.setNauthor(rs.getString("nauthor"));
                news.setNcreateDate(rs.getString("ncreateDate"));//时间类型
                news.setNtitle(rs.getString("ntitle"));
                news.setNpicPath(rs.getString("npicPath"));
                list.add(news);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return list;
    }

    /**
     * 通过id查询新闻
     *
     * @param nid
     * @return
     * @throws RuntimeException
     */
    @Override
    public News getByIdNews(int nid) throws RuntimeException {
        ResultSet result = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        News news = new News();
        try {
            //1.获取连接
            conn = DBUtil.getConnection();
            //2.执行sql
            String sql = "SELECT n.nid,n.nauthor,n.npicPath,n.ncontent,n.nmodifyDate FROM news n,comments c WHERE n.nid = c.cnid AND n.nid = ?;";

            //预处理对象
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,news.getNid());
            result = pstmt.executeQuery();
            //传递参数
            while(result.next()) {
                news.setNtid(result.getInt("nid"));
                news.setNauthor(result.getString("nauthor"));
                news.setNpicPath(result.getString("npicPath"));
                news.setNcontent(result.getString("ncontent"));
                news.setNmodifyDate(result.getString("nmodifyDate"));
            }

        }catch(Exception e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            DBUtil.closeAll(conn, pstmt, null);
            return news;
        }
    }

    /**
     * 查询某些条件下的新闻总数（1.所有新闻总数 2.某一个主题下的总数）
     *
     * @param news
     * @return
     * @throws RuntimeException
     */
    @Override
    public int getCount(News news) throws RuntimeException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int count = 0;
        try{
            //1.获取连接对象
            conn = DBUtil.getConnection();
            //2.执行sql
            String sql = "SELECT COUNT(0) AS total FROM news ";
            if(news.getNtid() != 0){sql += " WHERE ntid = ? ";}
            //3.获取预处理对象
            pstmt = conn.prepareStatement(sql);
            if(news.getNtid() != 0){pstmt.setInt(1,news.getNtid());}
            System.out.println(sql);
            //5.处理结果集
            rs = pstmt.executeQuery();
            while(rs.next()){
                count = rs.getInt("total");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return count;
    }

    /**
     * 分页获查询某些条件下的新闻（1.所有的新闻总数 2.某一个主题下的总数）
     *
     * @param currPageNo
     * @param pageSize
     * @param news
     * @return
     * @throws RuntimeException
     */
    @Override
    public List<News> getNewsByPage(int currPageNo, int pageSize, News news) throws RuntimeException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<News> list = new ArrayList<News>();
        try{
            //1.获取连接对象
            conn = DBUtil.getConnection();
            //2.执行sql
            String sql = "SELECT nid,ntid,ntitle,ncreateDate,npicPath FROM news ";
            if(news.getNtid() != 0){
                sql += " where ntid = ? ";
            }
            sql += " ORDER BY ncreateDate DESC LIMIT ?,?;";
            //3.获取预处理对象
            pstmt = conn.prepareStatement(sql);
            //4.设置参数
            if(news.getNtid() != 0){
                pstmt.setInt(1,news.getNtid());
                pstmt.setInt(2,(currPageNo-1)*pageSize);
                pstmt.setInt(3,pageSize);
            }else{
                pstmt.setInt(1,(currPageNo-1)*pageSize);
                pstmt.setInt(2,pageSize);
            }
            System.out.println(sql);
            //5.处理结果集
            rs = pstmt.executeQuery();
            while(rs.next()){
                News newsRow = new News();
                newsRow.setNid(rs.getInt("nid"));
                newsRow.setNtid(rs.getInt("ntid"));
                newsRow.setNcreateDate(rs.getTimestamp("ncreateDate"));//时间类型
                newsRow.setNtitle(rs.getString("ntitle"));
                newsRow.setNpicPath(rs.getString("npicPath"));
                list.add(newsRow);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return list;
    }
}
